package com.vimi8.ebb.service;

import com.vimi8.ebb.dto.CategoryDto;
import com.vimi8.ebb.dto.EnumCategoryDto;
import com.vimi8.ebb.dto.TrainInfoCountDto;
import com.vimi8.ebb.dto.TrainMemberDto;
import com.vimi8.ebb.model.EnumCategory;
import com.vimi8.ebb.model.GeoCodings;
import com.vimi8.ebb.model.ProductInfo;
import com.vimi8.ebb.model.TrainInfo;
import com.vimi8.ebb.sqlprovider.ElectricityBusinessSqlProvider;
import com.vimi8.ebb.sqlprovider.TrainInfoSqlProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * Created by think on 2016/9/8.
 * 枚举分类表service
 */
@Mapper
public interface TrainInfoService {

    /**
     * @return
     */
@Insert("insert into train_info(id,train_address,train_address_code,train_teacher,sex,phone,train_object,train_date,train_title,train_content,train_type,train_evaluate,train_peoples)" +
            " values(#{id},#{trainAddress},#{trainAddressCode},#{trainTeacher},#{sex},#{phone},#{trainObject},#{trainDate},#{trainTitle},#{trainContent},#{trainType},#{trainEvaluate},#{trainPeoples})")
    public int insertTrainInfo(TrainInfo trainInfo);

    @InsertProvider(type = TrainInfoSqlProvider.class, method = "getInsertTrainInfoSql")
    public int batchTrainInfo(@Param("trainInfoList")  List<TrainInfo> trainInfoList);

    @Update("update train_info set train_address=#{trainAddress},train_address_code=#{trainAddressCode},train_teacher=#{trainTeacher},sex=#{sex}," +
            "phone=#{phone},train_object=#{trainObject},train_date=#{trainDate},train_title=#{trainTitle},train_content=#{trainContent}," +
            "train_type=#{trainType},train_evaluate=#{trainEvaluate},train_peoples=#{trainPeoples}" +
            " where id=#{id}")
    public int updateTrainInfo(TrainInfo trainInfo);

    @Select("<script>select count(id) from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"startTime!=null and startTime!=''\"> and train_date  &gt;= #{startTime}</if> " +
            "<if test=\"endTime!=null and endTime!=''\"> and train_date  &lt; #{endTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "</script>")
    public int queryTrainInfoCount(@Param("trainTitle")String trainTitle,@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("addressCode")String addressCode);

    @Select("<script>select count(id) from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "</script>")
    public int queryFrontTrainInfoCount(@Param("trainTitle")String trainTitle,@Param("addressCode")String addressCode);



    @Select("<script>select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher,sex as sex,phone as phone,train_object as trainObject,train_date as trainDate,train_title as trainTitle,train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"startTime!=null and startTime!=''\"> and train_date  &gt;= #{startTime}</if> " +
            "<if test=\"endTime!=null and endTime!=''\"> and train_date  &lt; #{endTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "  order by train_date desc limit #{index},#{size} </script>")
    public List<TrainInfo> queryTraInfoList(@Param("trainTitle")String trainTitle,@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("addressCode")String addressCode,@Param("index")int index,@Param("size") int size);

    @Select("<script>select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher,sex as sex,phone as phone,train_object as trainObject,train_date as trainDate,train_title as trainTitle,train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "order by train_date <if test=\"isTrainDateDesc!=null and isTrainDateDesc!=''\">  desc</if> " +
            "  limit #{index},#{size} " +
            "</script>")
    public List<TrainInfo> queryFrontTraInfoListOrderByTrainDate(@Param("trainTitle")String trainTitle,@Param("addressCode")String addressCode,@Param("isTrainDateDesc")String isTrainDateDesc,@Param("index")int index,@Param("size") int size);
    @Select("<script>select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher,sex as sex,phone as phone,train_object as trainObject,train_date as trainDate,train_title as trainTitle,train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "order by train_peoples <if test=\"isTrainPeoplesDesc!=null and isTrainPeoplesDesc!=''\">  desc</if> " +
            "  limit #{index},#{size} " +
            "</script>")
    public List<TrainInfo> queryFrontTraInfoListOrderByTrainPeoples(@Param("trainTitle")String trainTitle,@Param("addressCode")String addressCode,@Param("isTrainPeoplesDesc")String isTrainPeoplesDesc,@Param("index")int index,@Param("size") int size);


    @Select("<script>select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher,sex as sex,phone as phone,train_object as trainObject," +
            "train_date as trainDate,train_title as trainTitle,train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples from train_info where 1=1 " +
            "<if test=\"trainTitle!=null and trainTitle!=''\"> and train_title like CONCAT('%',#{trainTitle},'%')</if> " +
            "<if test=\"startTime!=null and startTime!=''\"> and train_date  &gt;= #{startTime}</if> " +
            "<if test=\"endTime!=null and endTime!=''\"> and train_date  &lt; #{endTime}</if> " +
            "<if test=\"addressCode!=null and addressCode!=''\"> and train_address_code like CONCAT('%',#{addressCode},'%')</if> " +
            "  order by train_date desc </script>")
    public List<TrainInfo> queryTraInfoListForExcel(@Param("trainTitle")String trainTitle,@Param("startTime")String startTime,@Param("endTime")String endTime,@Param("addressCode")String addressCode);


    @Select("select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher," +
            "sex as sex,phone as phone,train_object as trainObject,train_date as trainDate,train_title as trainTitle," +
            "train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples" +
            " from train_info where 1=1 " +
            "  order by train_date desc")
    public List<TrainInfo> queryTraInfoAllList();


    @Select("select id,train_address as trainAddress,train_address_code as trainAddressCode,train_teacher as trainTeacher,sex as sex,phone as phone,train_object as trainObject,train_date as trainDate,train_title as trainTitle,train_content as trainContent ,train_type as trainType,train_evaluate as trainEvaluate ,train_peoples as trainPeoples from train_info where 1=1 and " +
            "id=#{id}")
    public TrainInfo queryTraInfoById(@Param("id")String id);
    @Delete(" delete from train_info where 1=1 and " +
            "id=#{id}")
    public int deleteTraInfoById(@Param("id")String id);


    /**
     * 批量删除培训信息
     * @param ids
     * @return
     */
    @Delete("DELETE t,m FROM train_info AS t LEFT JOIN train_members AS m ON " +
            "t.id=m.train_id WHERE t.id IN (${ids})")
    public Integer deleteTraInfoBySelectedIds(@Param("ids") String ids);



    //查询有培训信息的所有区县信息
    @Select("<script>select DISTINCT(g.id) as id,g.geo_name as geoName from train_info ti,geo_codings g where 1=1 and " +
            "ti.train_address_code=g.id " +
            "<if test=\"trainDate!=null and trainDate!=''\"> and ti.train_date like CONCAT('%',#{trainDate},'%')</if> " +
            "</script>")
    public List<GeoCodings> queryGeoCodeFromTrainInfo(@Param("trainDate")String trainDate);

    //按年份查询参与培训的人数
    @Select("<script>select IFNULL(sum(train_peoples),0) AS trainPeoples from train_info  where 1=1  " +
            "<if test=\"trainDate!=null and trainDate!=''\"> and train_date like CONCAT('%',#{trainDate},'%')</if> " +
            "</script>")
    public float queryTrainPeoplesTotal(@Param("trainDate")String trainDate);

    //按年份查询参与培训的人数
    @Select("<script>select IFNULL(sum(train_peoples),0) AS trainPeoples from train_info  where 1=1  " +
            "<if test=\"trainDate!=null and trainDate!=''\"> and train_date like CONCAT('%',#{trainDate},'%')</if> " +
            "<if test=\"geoCode!=null and geoCode!=''\"> and train_address_code like CONCAT('%',#{geoCode},'%')</if> " +
            "</script>")
    public int queryTrainPeoplesTotalByGeo(@Param("trainDate")String trainDate,@Param("geoCode")String geoCode);


    //按季度的时间区间来查询培训统计信息
    @Select("<script>select count(1) as trainInfoCount,IFNULL(sum(train_peoples),0) AS trainPeoplesTotal from train_info  where 1=1  " +
            "<if test=\"startTime!=null and startTime!=''\"> and train_date &gt;=#{trainDate} </if> " +
            "<if test=\"endTime!=null and endTime!=''\"> and train_date &lt;#{endTime} </if> " +
            "<if test=\"geoCode!=null and geoCode!=''\"> and train_address_code like CONCAT('%',#{geoCode},'%')</if> " +
            "</script>")
    public TrainInfoCountDto queryTrainPeoplesTotalForReport(@Param("startTime")String startTime, @Param("endTime")String endTime, @Param("geoCode")String geoCode);

    //按季度的时间区间来查询培训统计信息 按月分组
    @Select("<script>select count(1) as trainInfoCount,IFNULL(sum(train_peoples),0) AS trainPeoplesTotal,MONTH(train_date) AS month from train_info  where 1=1  " +
            "<if test=\"startTime!=null and startTime!=''\"> and train_date &gt;=#{trainDate} </if> " +
            "<if test=\"endTime!=null and endTime!=''\"> and train_date &lt;#{endTime} </if> " +
            "<if test=\"geoCode!=null and geoCode!=''\"> and train_address_code like CONCAT('%',#{geoCode},'%')</if> GROUP BY MONTH(train_date)" +
            "</script>")
    public TrainInfoCountDto queryTrainGroupByMonthForReport(@Param("startTime")String startTime, @Param("endTime")String endTime, @Param("geoCode")String geoCode);
}



